import sqlite3
from app import app
from datetime import datetime
DATABASE = app.config['DATABASE']

def close(conn):
    conn.commit()
    conn.close()

def createDatabase():
    conn = sqlite3.connect(DATABASE)
    create_tb_cmd = '''CREATE TABLE IF NOT EXISTS test
       (ID integer PRIMARY KEY autoincrement,
       temperature     varchar (10)  NOT NULL,
       humidity        varchar (10)  NOT NULL,
       createTime      varchar (50))'''
    conn.execute(create_tb_cmd)
    print ("Table created successfully")
    close(conn)


def insertData():
    conn = sqlite3.connect(DATABASE)
    cur = conn.cursor()
    print ("Opened database successfully")
    sql = 'INSERT INTO test (temperature,humidity,createTime) VALUES (?,?,?)'
    val = ('23', '24', str(datetime.now().strftime('%Y-%m-%d %H:%M:%S')))
    cur.execute(sql, val)
    print ("Records created successfully")
    close(conn)

def deleteData():
    conn = sqlite3.connect(DATABASE)
    conn.execute(" DELETE FROM test WHERE date('now', '-30 day') >= date(createTime) ")
    print ("delete records successfully")
    close(conn)

    
